package com.us.usws.dao;

import java.util.List;

import net.iwsh.core.db.DBExecutor;
import net.iwsh.core.db.ext.SQLWithParams;
import net.iwsh.core.exception.DBException;
import net.iwsh.core.utils.IDGenerator;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.us.usws.common.SiteConstants;
import com.us.usws.model.domain.system.User;
import com.us.usws.model.flat.system.DOUser;
import com.us.usws.model.flat.system.DOUserAttr;
import com.us.usws.support.uitls.DataPageUtil;

public class UserDAO extends BasePageSearchDAO<User>{

	private static Log log = LogFactory.getFactory().getInstance(UserDAO.class);

	/**
	 * get SQLs
	 */
	private static final String SQL_GET_USER_BY_LOGINNAME_PASSWD = "select * from sys_user where login_name=? and passwd=?";
	private static final String SQL_GET_USER_BY_LOGINNAME = "select * from sys_user where login_name=?";
	private static final String SQL_GET_USER_BY_UID = "select * from sys_user where uid=?";
	private static final String SQL_GET_ATTR_BY_USER_UID = "select * from sys_user_attr where uid=?";
	private static final String SQL_GET_USERS_BY_DEPT = "select u.* from sys_user u inner join sys_dept d on d.uid=u.dept_uid where d.uid=?";
	private static final String SQL_GET_USERS_TOTALCOUNT = "select count(*) from sys_user u where is_valid=?";
	private static final String SQL_GET_USERS_BY_VALID = "select * from sys_user where is_valid=?";

	/**
	 * about user attr
	 */
	private static final String SQL_GET_USER_ATTR_COUNT_BY_UID = "select count(*) from sys_user_attr where uid=?";

	/**
	 * cud SQLs
	 */
	private static final String SQL_INSERT_USER = "insert into sys_user(uid,login_name,passwd,email_addr,full_name,gender_type,avatar,dept_uid,is_valid,is_site_admin,role) values (?,?,?,?,?,?,?,?,?,?,?)";
	private static final String SQL_DELETE_USER = "delete from sys_user where uid=?";
	private static final String SQL_UPDATE_IS_VALID = "update sys_user set is_valid=? where uid=?";
	private static final String SQL_UPDATE_USER = "update sys_user set login_name=?, passwd=?, email_addr=?, full_name=?, gender_type=?, avatar=?, dept_uid=?, is_valid=?, is_site_admin=?, role=? where uid=?";
	private static final String SQL_UPDATE_AVATAR = "update sys_user set avatar=? where uid=?";

	/**
	 * about user attr
	 */
	private static final String SQL_INSERT_USERATTR = "insert into sys_user_attr(uid,province_code,city_code,strict_code,mobile_no,contact_addr,birth_date,qq_no) values (?,?,?,?,?,?,?,?)";
	private static final String SQL_UPDATE_USERATTR = "update sys_user_attr set province_code=?, city_code=?, strict_code=?, mobile_no=?, contact_addr=?, birth_date=?, qq_no=? where uid=?";

	private static final String SQL_UPDATE_PASSWORD = "update sys_user set passwd=? where uid=?";

	private DBExecutor dbe = new DBExecutor();

	/**
	 * get User by uid
	 * @param uid
	 * @return
	 */
	public User getUser(String uid) {
		User u = dbe.load(SQL_GET_USER_BY_UID, User.class, uid);
		fillProps(u);
		return u;
	}

	/**
	 * get DOUser, not fetch too many SQL executions
	 * @param uid
	 * @return
	 */
	public DOUser getDOUser(String uid) {
		DOUser ret = dbe.load(SQL_GET_USER_BY_UID, DOUser.class, uid);
		return ret;
	}

	/**
	 * get User by loginName and passwd
	 * @param loginName
	 * @param md5Passwd
	 * @return
	 */
	public User getUserByLoginNameMD5Passwd(String loginName, String md5Passwd) {
		User ret = dbe.load(SQL_GET_USER_BY_LOGINNAME_PASSWD, User.class, loginName, md5Passwd);
		fillProps(ret);
		return ret;
	}

	/**
	 * get user by login name
	 * @param loginName
	 * @return
	 */
	public User getUserByLoginName(String loginName) {
		User ret = dbe.load(SQL_GET_USER_BY_LOGINNAME, User.class, loginName);
		fillProps(ret);
		return ret;
	}

	/**
	 * get DO user by login info.
	 * @param loginName
	 * @param passwd
	 * @return
	 */
	public DOUser getDOUserByLogin(String loginName, String md5Passwd) {
		DOUser ret = dbe.load(SQL_GET_USER_BY_LOGINNAME_PASSWD, DOUser.class, loginName, md5Passwd);
		return ret;
	}

	/**
	 * get DO user by login name, prevent to load the other attributes of the full user
	 * @param loginName
	 * @return
	 */
	public DOUser getDOUserByLoginName(String loginName) {
		DOUser ret = dbe.load(SQL_GET_USER_BY_LOGINNAME, DOUser.class, loginName);
		return ret;
	}

	/**
	 * get users by dept
	 * @param deptUid
	 * @return
	 */
	public List<User> getUsersByDept(String deptUid) {
		List<User> users = dbe.list(SQL_GET_USERS_BY_DEPT, User.class, deptUid);
		for (User each : users) {
			fillProps(each);
		}
		return users;
	}

	/**
	 * get users totalCount
	 * @return
	 */
	public int getUsersTotalCount() {
		return dbe.getCount(SQL_GET_USERS_TOTALCOUNT, SiteConstants.STR_BOOLEAN_YES);
	}

	/**
	 * paging get users
	 * @param start 
	 * @param length
	 * @return
	 */
	public void loadUsersByPaging(DataPageUtil<User> dataPageUtil) {
		String countSql = "select count(*) from sys_user where 1=1";
		String sql = " select * from sys_user where 1=1";
		SQLWithParams swp = initSQLWithParams(dataPageUtil, null, countSql, sql);
		dataPageUtil.setResult(dbe.list(swp.getSql(), User.class, swp.getParams()));
	}
//		return dbe.list(SQL_GET_USERS_PAGING, User.class, SiteConstants.STR_BOOLEAN_YES, start, length);
//	}

	/**
	 * get all valid users
	 * @return
	 */
	public List<User> getAllValidUsers() {
		List<User> users = dbe.list(SQL_GET_USERS_BY_VALID, User.class, SiteConstants.STR_BOOLEAN_YES);
		for (User each : users) {
			fillProps(each);
		}
		return users;
	}

	/**
	 * insert user
	 * @param u
	 * @return
	 */
	public String insertUser(User u) {
		String uid = IDGenerator.getInstance().genUID();
		u.setUid(uid);
		dbe.startTransaction();
		try {
			dbe.executeBatch(SQL_INSERT_USER, u.getUid(), u.getLoginName(), u.getPasswd(), u.getEmailAddr(), u.getFullName(),
					u.getGenderType(), u.getAvatar(), u.getDeptUid(), u.getIsValid(), u.getIsSiteAdmin(), u.getRole());
			if (u.getUserAttr() != null) {
				DOUserAttr ua = u.getUserAttr();
				dbe.executeBatch(SQL_INSERT_USERATTR, ua.getUid(), ua.getProvinceCode(), ua.getCityCode(), ua.getStrictCode(),
						ua.getMobileNo(), ua.getContactAddr(), ua.getBirthDate(), ua.getQqNo());
			}
		} catch (Exception e) {
			log.error("Error when insert user.", e);
			dbe.rollbackTransaction();
			throw new DBException("Batch insert error.", e);
		}
		dbe.commitTransaction();
		return uid;
	}

	/**
	 * delete user
	 * @param uid
	 */
	public void deleteUser(String uid) {
		dbe.execute(SQL_DELETE_USER, uid);
	}

	/**
	 * update isvalid flag
	 * @param uid
	 * @param valid
	 */
	public void updateValid(String uid, String valid) {
		dbe.execute(SQL_UPDATE_IS_VALID, valid, uid);
	}

	/**
	 * update user
	 * @param user
	 */
	public void update(User user) {
		dbe.startTransaction();
		try {
			dbe.executeBatch(SQL_UPDATE_USER, user.getLoginName(), user.getPasswd(), user.getEmailAddr(), user.getFullName(),
					user.getGenderType(), user.getAvatar(), user.getDeptUid(), user.getIsValid(), user.getIsSiteAdmin(), user.getRole(),
					user.getUid());

			//user attr
			if (user.getUserAttr() != null) {
				DOUserAttr userAttr = user.getUserAttr();
				if (hasUserAttr(userAttr.getUid())) {
					dbe.executeBatch(SQL_UPDATE_USERATTR, userAttr.getProvinceCode(), userAttr.getCityCode(), userAttr.getStrictCode(),
							userAttr.getMobileNo(), userAttr.getContactAddr(), userAttr.getBirthDate(), userAttr.getQqNo(),
							userAttr.getUid());
				} else {
					dbe.executeBatch(SQL_INSERT_USERATTR, userAttr.getUid(), userAttr.getProvinceCode(), userAttr.getCityCode(),
							userAttr.getStrictCode(), userAttr.getMobileNo(), userAttr.getContactAddr(), userAttr.getBirthDate(),
							userAttr.getQqNo());
				}
			}

			//others?

		} catch (Exception e) {
			dbe.rollbackTransaction();
			throw new DBException("Batch update error.", e);
		} finally {
			dbe.commitTransaction();
		}
	}

	/**
	 * whether has user attr
	 * @param uid
	 * @return
	 */
	private boolean hasUserAttr(String uid) {
		int cnt = dbe.getCount(SQL_GET_USER_ATTR_COUNT_BY_UID, uid);
		return cnt > 0 ? true : false;
	}

	/**
	 * fill props
	 * @param ret
	 */
	private void fillProps(User u) {
		if (u != null) {
			DOUserAttr attr = dbe.load(SQL_GET_ATTR_BY_USER_UID, DOUserAttr.class, u.getUid());
			u.setUserAttr(attr);
		}
	}

	/**
	 * update avatar
	 * @param uid
	 * @param avatar
	 */
	public void updateAvatar(String uid, String avatar) {
		dbe.execute(SQL_UPDATE_AVATAR, avatar, uid);
	}

	public void updatePassword(String newPasswd, String userUid) {
		dbe.execute(SQL_UPDATE_PASSWORD, newPasswd, userUid);
	}

}
